<?php
namespace app\backman\controller;
use app\backman\model\Meal;
use \app\common\controller\AuthBack;
use app\common\model\Copywx;
use app\common\model\Scurl;
use app\common\model\CusCategory;
use lib\CashStatus;
use think\Db;
use think\facade\Env;
use Qiniu\Auth;
use Qiniu\Storage\BucketManager;
use Qiniu\Storage\UploadManager;
use Intervention\Image;
use GuzzleHttp;
use think\facade\App;
require_once App::getRootPath().'/extend/PHPExcel/PHPExcel.php';

class Customer extends AuthBack{


    public function initialize(){
        parent::initialize();
    }

    public function index(){
        $model = new Copywx;
        $limit = isset($_GET['limit']) ? $_GET['limit'] : '20';
        $where = [];
       
        if( !empty(input('title')) ){
            $where[] = ['static', 'like', "%".input('title')."%"];
        }
        if( !empty(input('urlid')) ){
            $where[] = ['host', 'eq', input('urlid')];
        }
        if( !empty(input('start_time')) && !empty(input('end_time')) ){
            $where[] = ['created_time','between',input('start_time').','.input('end_time')];
        }else{
            if( !empty(input('start_time')) ){
                $where[] = ['created_time','>=',input('start_time')];
            }
    
            if( !empty(input('end_time')) ){
                $where[] = ['created_time','<=',input('end_time')];
            }
        }

        $totalNum = Copywx::where($where)->count();
        $Scurl = Scurl::select();
        
        if( !empty(input('excel')) ){
            if( empty(input('urlid')) ) return $this->error('域名不能为空');
            $url = DB::name('scurl')->where('id',input('urlid'))->value('url');
            $excelName = $url.date('YmdHis');
            $nList = Copywx::alias('c')->join('Scurl s', 's.id = c.host')->where($where)->field('c.*,s.url')->select();
            $this->exportExcel($nList,$excelName);
        }

        if(request()->isAjax()){
            $page = isset($_GET['page']) ? $_GET['page'] : '1';
            $limit = isset($_GET['limit']) ? $_GET['limit'] : '20';
            $startNum = ($page - 1) * $limit;
            $totalPage = ceil($totalNum/$limit);
            $list = Copywx::alias('c')
                            ->join('Scurl s', 's.id = c.host')
                            ->where($where)->limit($startNum.','.$limit)
                            ->field('c.*,s.url')
                            ->order('created_time desc')->select();
            if(empty($list)){
                return json(['code'=>0,'data'=>[],'total'=>$totalPage,'count'=>$totalNum]);
            }elseif($page > $totalPage){
                return json(['code'=>0,'data'=>[],'total'=>$totalPage+1,'count'=>$totalNum]);
            }else{
                $nList = array();
                if(!empty($list)){
                    foreach($list as $k=>$vo){
                        $nList[$k] = $vo;
                        $new = new IpLocation(); 
                        $ip = $new->getlocation($vo['ip']); 
                        $nList[$k]['ip'] = mb_convert_encoding($ip['country'],'gbk','utf-8').'-'.mb_convert_encoding($ip['area'],'gbk','utf-8');
                        $nList[$k]['op'] = url('option',['id'=>$vo['id']]);
                    }
                }
                $return = [
                    'code'=>0,
                    'msg'=>'',
                    'count'=>$totalNum,
                    'data'=>$nList
                ];
                return json($return);
            }
        }else{
            $this->assign('totalNum',$totalNum);
            $this->assign('Scurl',$Scurl);
            return view();
        }
    }
    






    /**
     * 删除
     */
    public function del(){
        $count = DB::name('order')->where('cid',$this->data['id'])->count();
        if( $count ){
            return $this->error('该客户有绑定的订单，无法删除');
        }else{
            AdminLog($this->admin['id'],'删除客户【'.$this->data['id'].'】信息');
            $res = DB::name('customer')->where('id',$this->data['id'])->delete();
            if( $res ){
                return $this->success('操作成功',url('index'));
            }else{
                return $this->error('删除失败');
            }
        }

    }


    public function download(){
        $model = new Copywx;
        $limit = isset($_GET['limit']) ? $_GET['limit'] : '20';
        $where = [];
       
        if( !empty(input('title')) ){
            $where[] = ['static', 'like', "%".input('title')."%"];
        }
        if( !empty(input('urlid')) ){
            $where[] = ['host', 'eq', input('urlid')];
        }
        if( !empty(input('start_time')) && !empty(input('end_time')) ){
            $where[] = ['created_time','between',input('start_time').','.input('end_time')];
        }else{
            if( !empty(input('start_time')) ){
                $where[] = ['created_time','>=',input('start_time')];
            }
    
            if( !empty(input('end_time')) ){
                $where[] = ['created_time','<=',input('end_time')];
            }
        }

        if( empty(input('urlid')) ) return $this->error('域名不能为空');
        $url = DB::name('scurl')->where('id',input('urlid'))->value('url');
        $excelName = $url.date('YmdHis');
        $nList = Copywx::alias('c')->join('Scurl s', 's.id = c.host')->where($where)->field('c.*,s.url')->select();
        $this->exportExcel($nList,$excelName);

        return $this->error('导出失败');
    }

    /**

     * 数据导出 Excel

     */

    public function exportExcel($data, $name){
        $objPHPExcel = new \PHPExcel();  //实例化  相当于在桌面新建一个excel
            // 表头
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '序号')
                ->setCellValue('B1', '关键词')
                ->setCellValue('C1', '域名')
                ->setCellValue('D1', 'ip')
                ->setCellValue('E1', '录入时间');
            //设置内容水平居中
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            // 设置个表格宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(24);
            //设置单元格自动宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
            // 内容
            for ($i = 0, $len = count($data); $i < $len; $i++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 2), $data[$i]['id']);     //序号
                $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 2), $data[$i]['static']);   // 关键词
                $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 2), $data[$i]['url'], \PHPExcel_Cell_DataType::TYPE_STRING);       // 检测单号
                $new = new IpLocation(); 
                $ip = $new->getlocation($data[$i]['ip']); 
                $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 2), mb_convert_encoding($ip['country'],'gbk','utf-8').'-'.mb_convert_encoding($ip['area'],'gbk','utf-8'));  // 客户名称
                $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 2), $data[$i]['created_time']);          // 建单人
                //设置内容水平居中
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
                $objPHPExcel->getActiveSheet()->getStyle('J' . ($i + 2))->getAlignment()->setWrapText(true);
            }
            $objPHPExcel->setActiveSheetIndex(0);
            ob_end_clean();//清楚缓存区，解决乱码问题
            header("Pragma:public");
            
            header("Content-Type:application/x-msexecl;name=\"{$name}.xlsx\"");
            header("Content-Disposition:inline;filename=\"{$name}.xlsx\"");
            
            $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
            
            $objWriter->save('php://output');
            exit;
    }








}






/**
 * 根据用户IP获取用户地理位置
 * $ip  用户ip
 */
class IpLocation { 
    /** 
     * QQWry.Dat文件指针 
     * 
     * @var resource 
     */ 
    private $fp; 
 
    /** 
     * 第一条IP记录的偏移地址 
     * 
     * @var int 
     */ 
    private $firstip; 
 
    /** 
     * 最后一条IP记录的偏移地址 
     * 
     * @var int 
     */ 
    private $lastip; 
 
    /** 
     * IP记录的总条数（不包含版本信息记录） 
     * 
     * @var int 
     */ 
    private $totalip; 
 
    /** 
     * 构造函数，打开 QQWry.Dat 文件并初始化类中的信息 
     * 
     * @param string $filename 
     * @return IpLocation 
     */ 
    public function __construct($filename = "UTFWryutf8.dat") { 
        $this->fp = 0; 
        if (($this->fp      = fopen(App::getRootPath().'public/static/'.$filename, 'rb')) !== false) { 
            $this->firstip  = $this->getlong(); 
            $this->lastip   = $this->getlong(); 
            $this->totalip  = ($this->lastip - $this->firstip) / 7; 
        } 
    } 
 
    /** 
     * 返回读取的长整型数 
     * 
     * @access private 
     * @return int 
     */ 
    private function getlong() { 
        //将读取的little-endian编码的4个字节转化为长整型数 
        $result = unpack('Vlong', fread($this->fp, 4)); 
        return $result['long']; 
    } 
 
    /** 
     * 返回读取的3个字节的长整型数 
     * 
     * @access private 
     * @return int 
     */ 
    private function getlong3() { 
        //将读取的little-endian编码的3个字节转化为长整型数 
        $result = unpack('Vlong', fread($this->fp, 3).chr(0)); 
        return $result['long']; 
    } 
 
    /** 
     * 返回压缩后可进行比较的IP地址 
     * 
     * @access private 
     * @param string $ip 
     * @return string 
     */ 
    private function packip($ip) { 
        // 将IP地址转化为长整型数，如果在PHP5中，IP地址错误，则返回False， 
        // 这时intval将Flase转化为整数-1，之后压缩成big-endian编码的字符串 
        return pack('N', intval(ip2long($ip))); 
    } 
 
    /** 
     * 返回读取的字符串 
     * 
     * @access private 
     * @param string $data 
     * @return string 
     */ 
    private function getstring($data = "") { 
        $char = fread($this->fp, 1); 
        while (ord($char) > 0) {        // 字符串按照C格式保存，以\0结束 
            $data  .= $char;             // 将读取的字符连接到给定字符串之后 
            $char   = fread($this->fp, 1); 
        } 
        return $data; 
    } 
 
    /** 
     * 返回地区信息 
     * 
     * @access private 
     * @return string 
     */ 
    private function getarea() { 
        $byte = fread($this->fp, 1);    // 标志字节 
        switch (ord($byte)) { 
            case 0:                     // 没有区域信息 
                $area = ""; 
                break; 
            case 1: 
            case 2:                     // 标志字节为1或2，表示区域信息被重定向 
                fseek($this->fp, $this->getlong3()); 
                $area = $this->getstring(); 
                break; 
            default:                    // 否则，表示区域信息没有被重定向 
                $area = $this->getstring($byte); 
                break; 
        } 
        return $area; 
    } 
    private $provinces = array("黑龙江省","辽宁省","吉林省","河北省","河南省","湖北省","湖南省","山东省","山西省","陕西省", 
                                  "安徽省","浙江省","江苏省","福建省","广东省","海南省","四川省","云南省","贵州省","青海省","甘肃省", 
                                  "江西省","台湾省","内蒙古","宁夏","新疆","西藏","广西","北京市","上海市","天津市","重庆市","香港","澳门"); 
    /** 
     * 根据所给 IP 地址或域名返回所在地区信息 
     * 
     * @access public 
     * @param string $ip 
     * @return array 
     */ 
    public function getlocation($ip='') { 
        if (!$this->fp) return null;            // 如果数据文件没有被正确打开，则直接返回空 
        if(empty($ip)) $ip = get_client_ip(); 
        $location['ip'] = gethostbyname($ip);   // 将输入的域名转化为IP地址 
        $ip = $this->packip($location['ip']);   // 将输入的IP地址转化为可比较的IP地址 
                                                // 不合法的IP地址会被转化为255.255.255.255 
        // 对分搜索 
        $l = 0;                         // 搜索的下边界 
        $u = $this->totalip;            // 搜索的上边界 
        $findip = $this->lastip;        // 如果没有找到就返回最后一条IP记录（QQWry.Dat的版本信息） 
        while ($l <= $u) {              // 当上边界小于下边界时，查找失败 
            $i = floor(($l + $u) / 2);  // 计算近似中间记录 
            fseek($this->fp, $this->firstip + $i * 7); 
            $beginip = strrev(fread($this->fp, 4));     // 获取中间记录的开始IP地址 
            // strrev函数在这里的作用是将little-endian的压缩IP地址转化为big-endian的格式 
            // 以便用于比较，后面相同。 
            if ($ip < $beginip) {       // 用户的IP小于中间记录的开始IP地址时 
                $u = $i - 1;            // 将搜索的上边界修改为中间记录减一 
            } 
            else { 
                fseek($this->fp, $this->getlong3()); 
                $endip = strrev(fread($this->fp, 4));   // 获取中间记录的结束IP地址 
                if ($ip > $endip) {     // 用户的IP大于中间记录的结束IP地址时 
                    $l = $i + 1;        // 将搜索的下边界修改为中间记录加一 
                } 
                else {                  // 用户的IP在中间记录的IP范围内时 
                    $findip = $this->firstip + $i * 7; 
                    break;              // 则表示找到结果，退出循环 
                } 
            } 
        } 
 
        //获取查找到的IP地理位置信息 
        fseek($this->fp, $findip); 
        $location['beginip'] = long2ip($this->getlong());   // 用户IP所在范围的开始地址 
        $offset = $this->getlong3(); 
        fseek($this->fp, $offset); 
        $location['endip'] = long2ip($this->getlong());     // 用户IP所在范围的结束地址 
        $byte = fread($this->fp, 1);    // 标志字节 
        switch (ord($byte)) { 
            case 1:                     // 标志字节为1，表示国家和区域信息都被同时重定向 
                $countryOffset = $this->getlong3();         // 重定向地址 
                fseek($this->fp, $countryOffset); 
                $byte = fread($this->fp, 1);    // 标志字节 
                switch (ord($byte)) { 
                    case 2:             // 标志字节为2，表示国家信息又被重定向 
                        fseek($this->fp, $this->getlong3()); 
                        $location['country']    = $this->getstring(); 
                        fseek($this->fp, $countryOffset + 4); 
                        $location['area']       = $this->getarea(); 
                        break; 
                    default:            // 否则，表示国家信息没有被重定向 
                        $location['country']    = $this->getstring($byte); 
                        $location['area']       = $this->getarea(); 
                        break; 
                } 
                break; 
            case 2:                     // 标志字节为2，表示国家信息被重定向 
                fseek($this->fp, $this->getlong3()); 
                $location['country']    = $this->getstring(); 
                fseek($this->fp, $offset + 8); 
                $location['area']       = $this->getarea(); 
                break; 
            default:                    // 否则，表示国家信息没有被重定向 
                $location['country']    = $this->getstring($byte); 
                $location['area']       = $this->getarea(); 
                break; 
        } 
        if (trim($location['country']) == 'CZ88.NET') {  // CZ88.NET表示没有有效信息 
            $location['country'] = '未知'; 
        } 
        if (trim($location['area']) == 'CZ88.NET') { 
            $location['area'] = ''; 
        } 
        $location['country'] = @iconv('gbk','utf-8',$location['country']); //转换格式，防止乱码 
        $location['area'] = @iconv('gbk','utf-8',$location['area']); //转换格式，防止乱码 
        foreach($this->provinces as $v) { 
            if(strpos($location['country'],$v) === 0) { 
                $location['province'] = $v; 
                $location['city'] = str_replace($v,'',$location['country']); 
                break; 
            } 
        } 
        if(empty($location['province'])) $location['province'] = $location['country']; 
        if(empty($location['city'])) $location['city'] = $location['country']; 
        return $location; 
    } 
 
    /** 
     * 析构函数，用于在页面执行结束后自动关闭打开的文件。 
     * 
     */ 
    public function __destruct() { 
        if ($this->fp) { 
            fclose($this->fp); 
        } 
        $this->fp = 0; 
    } 
 
} 